package com.xxxy.jsq.dao;

import com.xxxy.jsq.dbhelper.C3P0Utils;
import com.xxxy.jsq.model.Rule;
import com.xxxy.jsq.model.Page;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class RuleDao implements BaseDao<Rule> {
    private C3P0Utils db = null;

    public RuleDao() {
        this.db = new C3P0Utils();
    }

    @Override
    public String add(Rule model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("insert into pa_rule(rule_id, level_id, works_id, rule_count, rule_years, rule_memo, rule_flag) ");
        sqlStr.append("values(?,?,?,?,?,?,?)");
        conn = db.getConnection();
        boolean flag = isSame(model, true);
        if (flag == false) {
            int num = 0;
            try {
                pstm = conn.prepareStatement(sqlStr.toString());
                pstm.setLong(1, model.getRuleId());
                pstm.setLong(2, model.getLevelId());
                pstm.setLong(3, model.getWorksId());
                pstm.setInt(4, model.getRuleCount());
                pstm.setInt(5, model.getRuleYears());
                pstm.setString(6, model.getRuleMemo());
                pstm.setString(7, model.getRuleFlag());
                num = pstm.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            if (num > 0) {
                return "Ok";
            } else {
                return "Err";
            }
        } else {
            return "Same";
        }
    }

    @Override
    public String update(Rule model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        boolean flag = isSame(model, false);
        if (flag == true) {
            return "Same";
        }
        sqlStr.append("update pa_rule set ");
        if (model.getLevelId() != null) {
            sqlStr.append(" level_id=? ");
        }
        if (model.getWorksId() != null) {
            sqlStr.append(" ,works_id=? ");
        }
        if (model.getRuleCount() != null) {
            sqlStr.append(" ,rule_count=? ");
        }
        if (model.getRuleYears() != null) {
            sqlStr.append(" ,rule_years=? ");
        }
        if (model.getRuleMemo() != null) {
            sqlStr.append(" ,rule_memo=? ");
        }
        if (model.getRuleFlag() != null) {
            sqlStr.append(" ,rule_flag=? ");
        }
        sqlStr.append(" where rule_id=? ");
        int num = 0;
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            int index = 1;
            if (model.getLevelId() != null) {
                pstm.setLong(index++, model.getLevelId());
            }
            if (model.getWorksId() != null) {
                pstm.setLong(index++, model.getWorksId());
            }
            if (model.getRuleCount() != null) {
                pstm.setInt(index++, model.getRuleCount());
            }
            if (model.getRuleYears() != null) {
                pstm.setInt(index++, model.getRuleYears());
            }
            if (model.getRuleMemo() != null) {
                pstm.setString(index++, model.getRuleMemo());
            }
            if (model.getRuleFlag() != null) {
                pstm.setString(index++, model.getRuleFlag());
            }
            pstm.setLong(index, model.getRuleId());
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        if (num > 0) {
            return "Ok";
        } else {
            return "Err";
        }
    }

    @Override
    public String delete(Rule model) {
        return null;
    }

    @Override
    public String deleteById(Long cid) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("delete from pa_rule where rule_id=? ");
        conn = db.getConnection();
        int num = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        if (num > 0) {
            return "Ok";
        } else {
            return "Err";
        }
    }

    @Override
    public List<Rule> getAllData(Rule model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Rule> list = new ArrayList<Rule>();
        sqlStr.append("SELECT * FROM pa_rule ");
        sqlStr.append("WHERE 1=1 ");
        if (model.getRuleId() != null) {
            sqlStr.append("AND rule_id=? ");
        }
        if (model.getLevelId() != null) {
            sqlStr.append("AND level_id=? ");
        }
        if (model.getWorksId() != null) {
            sqlStr.append("AND works_id=? ");
        }
        if (model.getRuleCount() != null) {
            sqlStr.append("AND rule_count=? ");
        }
        if (model.getRuleYears() != null) {
            sqlStr.append("AND rule_years=? ");
        }
        if (model.getRuleMemo() != null) {
            sqlStr.append("AND rule_memo=? ");
        }
        if (model.getRuleFlag() != null) {
            sqlStr.append("AND rule_flag=? ");
        }
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            int index = 1;
            if (model.getRuleId() != null) {
                pstm.setLong(index++, model.getRuleId());
            }
            if (model.getLevelId() != null) {
                pstm.setLong(index++, model.getLevelId());
            }
            if (model.getWorksId() != null) {
                pstm.setLong(index++, model.getWorksId());
            }
            if (model.getRuleCount() != null) {
                pstm.setInt(index++, model.getRuleCount());
            }
            if (model.getRuleYears() != null) {
                pstm.setInt(index++, model.getRuleYears());
            }
            if (model.getRuleMemo() != null) {
                pstm.setString(index++, model.getRuleMemo());
            }
            if (model.getRuleFlag() != null) {
                pstm.setString(index++, model.getRuleFlag());
            }
            rs = pstm.executeQuery();
            while (rs.next()) {
                Rule rule = new Rule();
                rule.setRuleId(rs.getLong("rule_id"));
                rule.setLevelId(rs.getLong("level_id"));
                rule.setWorksId(rs.getLong("works_id"));
                rule.setRuleCount(rs.getInt("rule_count"));
                rule.setRuleYears(rs.getInt("rule_years"));
                rule.setRuleMemo(rs.getString("rule_memo"));
                rule.setRuleFlag(rs.getString("rule_flag"));
                list.add(rule);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Rule findById(Long cid) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT * FROM pa_rule WHERE rule_id=? ");
        conn = db.getConnection();
        Rule rule = new Rule();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            rs = pstm.executeQuery();
            if (rs.next()) {
                rule.setRuleId(rs.getLong("rule_id"));
                rule.setLevelId(rs.getLong("level_id"));
                rule.setWorksId(rs.getLong("works_id"));
                rule.setRuleCount(rs.getInt("rule_count"));
                rule.setRuleYears(rs.getInt("rule_years"));
                rule.setRuleMemo(rs.getString("rule_memo"));
                rule.setRuleFlag(rs.getString("rule_flag"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rule;
    }

    @Override
    public Rule find(Rule model) {
        return null;
    }


    @Override
    public boolean isSame(Rule model, boolean sflag) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT * FROM pa_rule WHERE rule_id=? ");
        if (sflag == false) {
            sqlStr.append("AND rule_id!=? ");
        }
        conn = db.getConnection();
        boolean flag = false;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, model.getRuleId());
            if (!sflag) {
                pstm.setLong(2, model.getRuleId());
            }
            rs = pstm.executeQuery();
            if (rs.next()) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }

    @Override
    public List<Rule> getAllDataByPage(Rule model, Page page) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Rule> list = new ArrayList<Rule>();
        sqlStr.append("SELECT * FROM pa_rule ");
        sqlStr.append("WHERE 1=1 ");
        if (model.getRuleId() != null) {
            sqlStr.append("AND rule_id=? ");
        }
        if (model.getLevelId() != null) {
            sqlStr.append("AND level_id=? ");
        }
        if (model.getWorksId() != null) {
            sqlStr.append("AND works_id=? ");
        }
        if (model.getRuleCount() != null) {
            sqlStr.append("AND rule_count=? ");
        }
        if (model.getRuleYears() != null) {
            sqlStr.append("AND rule_years=? ");
        }
        if (model.getRuleMemo() != null) {
            sqlStr.append("AND rule_memo=? ");
        }
        if (model.getRuleFlag() != null) {
            sqlStr.append("AND rule_flag=? ");
        }
        sqlStr.append("ORDER BY rule_id ");
        sqlStr.append("LIMIT ?,? ");
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            int index = 1;
            if (model.getRuleId() != null) {
                pstm.setLong(index++, model.getRuleId());
            }
            if (model.getLevelId() != null) {
                pstm.setLong(index++, model.getLevelId());
            }
            if (model.getWorksId() != null) {
                pstm.setLong(index++, model.getWorksId());
            }
            if (model.getRuleCount() != null) {
                pstm.setInt(index++, model.getRuleCount());
            }
            if (model.getRuleYears() != null) {
                pstm.setInt(index++, model.getRuleYears());
            }
            if (model.getRuleMemo() != null) {
                pstm.setString(index++, model.getRuleMemo());
            }
            if (model.getRuleFlag() != null) {
                pstm.setString(index++, model.getRuleFlag());
            }
            pstm.setInt(index++, (page.getCurrentPage() - 1) * page.getCount());
            pstm.setInt(index, page.getCount());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Rule rule = new Rule();
                rule.setRuleId(rs.getLong("rule_id"));
                rule.setLevelId(rs.getLong("level_id"));
                rule.setWorksId(rs.getLong("works_id"));
                rule.setRuleCount(rs.getInt("rule_count"));
                rule.setRuleYears(rs.getInt("rule_years"));
                rule.setRuleMemo(rs.getString("rule_memo"));
                rule.setRuleFlag(rs.getString("rule_flag"));
                list.add(rule);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int getCount(Rule model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT COUNT(*) FROM pa_rule ");
        sqlStr.append("WHERE 1=1 ");
        if (model.getRuleId() != null) {
            sqlStr.append("AND rule_id=? ");
        }
        if (model.getLevelId() != null) {
            sqlStr.append("AND level_id=? ");
        }
        if (model.getWorksId() != null) {
            sqlStr.append("AND works_id=? ");
        }
        if (model.getRuleCount() != null) {
            sqlStr.append("AND rule_count=? ");
        }
        if (model.getRuleYears() != null) {
            sqlStr.append("AND rule_years=? ");
        }
        if (model.getRuleMemo() != null) {
            sqlStr.append("AND rule_memo=? ");
        }
        if (model.getRuleFlag() != null) {
            sqlStr.append("AND rule_flag=? ");
        }
        conn = db.getConnection();
        int total = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            int index = 1;
            if (model.getRuleId() != null) {
                pstm.setLong(index++, model.getRuleId());
            }
            if (model.getLevelId() != null) {
                pstm.setLong(index++, model.getLevelId());
            }
            if (model.getWorksId() != null) {
                pstm.setLong(index++, model.getWorksId());
            }
            if (model.getRuleCount() != null) {
                pstm.setInt(index++, model.getRuleCount());
            }
            if (model.getRuleYears() != null) {
                pstm.setInt(index++, model.getRuleYears());
            }
            if (model.getRuleMemo() != null) {
                pstm.setString(index++, model.getRuleMemo());
            }
            if (model.getRuleFlag() != null) {
                pstm.setString(index++, model.getRuleFlag());
            }
            rs = pstm.executeQuery();
            if (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    @Override
    public List<Rule> getAllky() {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Rule> list = new ArrayList<Rule>();
        sqlStr.append("SELECT * FROM pa_rule WHERE rule_flag='0' ");
        sqlStr.append("ORDER BY rule_id ");
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Rule rule = new Rule();
                rule.setRuleId(rs.getLong("rule_id"));
                rule.setLevelId(rs.getLong("level_id"));
                rule.setWorksId(rs.getLong("works_id"));
                rule.setRuleCount(rs.getInt("rule_count"));
                rule.setRuleYears(rs.getInt("rule_years"));
                rule.setRuleMemo(rs.getString("rule_memo"));
                rule.setRuleFlag(rs.getString("rule_flag"));
                list.add(rule);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}